package Model;

import Common.Utils;
import Model.Dto.UserAccount;
import Model.Dto.jaxb.UserAccounts.UserAccountType;
import Model.Dto.jaxb.UserAccounts.UserAccounts;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Quang Nhat
 */
public class UserAccountModel {

    public static String validate(UserAccount acc) {
        String res = "";
        if (acc.getUsername() == null || acc.getUsername().trim().length() == 0) {
            res += "Username cannot be null or empty.\n";
        }
        if (acc.getFullname() == null || acc.getFullname().trim().length() == 0) {
            res += "Fullname cannot be null or empty.\n";
        }
        return res.length() == 0 ? null : res;
    }

    public static boolean addUserAccount(UserAccount acc, String password) {
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement stm = null;
        try {
            password = Utils.getCheckSum(password);
            con = Utils.makeConnection();
            stm = con.prepareStatement("INSERT INTO Users "
                    + "VALUES(?, ?, ?, ?, ?)");
            stm.setString(1, acc.getUsername());
            stm.setString(2, password);
            stm.setString(3, acc.getRole());
            stm.setNString(4, acc.getFullname());
            stm.setBoolean(5, acc.isActive());
            return stm.executeUpdate() > 0;
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return false;
    }

    public static boolean setUserAccountPassword(String username, String password) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("UPDATE Users "
                    + " SET password = ?"
                    + " WHERE username = ?");
            password = Utils.getCheckSum(password);
            stm.setString(1, password);
            stm.setString(2, username);
            return stm.executeUpdate() > 0;
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return false;
    }

    public static boolean updateUserAccount(UserAccount acc) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("UPDATE Users "
                    + " SET fullname = ?, role = ?, active = ? "
                    + " WHERE username = ?");
            stm.setNString(1, acc.getFullname());
            stm.setString(2, acc.getRole());
            stm.setBoolean(3, acc.isActive());
            stm.setString(4, acc.getUsername());
            return stm.executeUpdate() > 0;
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return false;
    }

    public static UserAccount getUserAccount(String username, String password) {
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement stm = null;
        try {
            password = Utils.getCheckSum(password);
            con = Utils.makeConnection();
            stm = con.prepareStatement("SELECT * FROM Users "
                    + "WHERE username = ? AND password = ? AND active = 1");
            stm.setString(1, username);
            stm.setString(2, password);
            rs = stm.executeQuery();
            if (rs.next()) {
                return new UserAccount(rs.getString("username"), rs.getString("role"), rs.getString("fullname"), true);
            }
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return null;
    }
    
    public static UserAccounts getJAXBAccounts() {
        List<UserAccount> accs = getAllUserAccount();
        UserAccounts rs = new UserAccounts();
        for (UserAccount a : accs) {
            UserAccountType at = new UserAccountType();
            at.setUsername(a.getUsername());
            at.setFullname(a.getFullname());
            at.setActive(a.isActive());
            at.setAdmin(a.isAdmin());
            at.setAccountant(a.isAccountant());
            rs.getAccount().add(at);
        }
        return rs;
    }
    
    public static List<UserAccount> getAllUserAccount() {
        List<UserAccount> result = new ArrayList<UserAccount>();
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement stm = null;
        try {
            con = Utils.makeConnection();
            stm = con.prepareStatement("SELECT * FROM Users");
            rs = stm.executeQuery();
            while (rs.next()) {
                result.add(new UserAccount(rs.getString("username"), rs.getString("role"), rs.getString("fullname"), rs.getBoolean("active")));
            }
        } catch (Exception ex) {
            Utils.ReportException(ex);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Utils.ReportException(ex);
                }
            }
        }
        return result;
    }
}
